<?

// it is not allowed to open a connection with the database until we need it.
class Query
{
    
    /**
     *
     * @var resource
     */
    private $dbc;
    /**
     *
     * @var <type>
     */
    private $connectionLink;
    /**
     *
     * @var <string>
     */
    private $queryStr;
    /**
     *
     * @var <resource>
     */
    private $result;
    /**
     *
     * @var <string>
     */
    private $queryType;
    /**
     *
     * @var <string>
     */
    private $error;
    /**
     *
     * @var <int>
     */
    private $errno;
    /**
     *
     * @var <int>
     */
    private $rowsNum;
    private $rowsFound;
    private $affectedRows;
    /**
     *
     * @var <int>
     */
    private $lastInsertID;
    private $stripSlashes;
    //The following attributes are espcially for query builder
    // Array of field names used in SELECT queries.
    private $fields;
    // Associative array of values used for INSERT and UPDATE queries.
    private $values;
    // Table name value used for INSERT INTO, UPDATE and FROM clauses.
    private $table;
    // Array of tables used for JOIN clauses.
    private $joins;
    // Array of conditions used for WHERE clause.
    private $where;
    // GROUP BY field
    private $group;
    
    private $having;
    // Array of field names used for ORDER BY.
    private $order;
    // LIMIT value
    private $limit;
    // Array of query string used for UNIONs (if more than one is built).
    private $queries = array();
    // If the SELECT is distinct.
    private $distinct;
    private $ignore;

    
    /**
     * Default Constructor
     *
     */
    public function __construct ()
    {
        
        $this->connectionLink = DatabaseConnection::connect("localhost", "root", "123", "cardescription");

        
        $num = func_num_args();
        $args = func_get_args();
        if ($num == 0)
        {
            $this->__emptyQueryConstruct();
        }
        elseif ($num == 1)
        {
            if (! is_array($args[0]))
            {
                $this->__singleQueryConstruct($args[0]);
            }
            else
            {
                $this->__multiQueriesConstruct($args[0]);
            }
        }
        elseif ($num == 2)
        {
            if (! is_array($args[0]))
            {
                $this->__singleQueryConstruct($args[0], $args[1]);
            }
            else
            {
                $this->__multiQueriesConstruct($args[0], $args[1]);
            }
        }
    }

    private function __emptyQueryConstruct ()
    {
        NULL;
    }

    private function __singleQueryConstruct ($queryStr, $transaction = FALSE)
    {
        

        $this->reset();
        $this->queryStr = trim($queryStr);
        $this->queryType($this->queryStr);
        $this->execute();
        
        if ($this->errno)
        {
            $this->rollback();
            $this->endTransaction();
            if (DEV_SERVER)
            {
                echo $this->getError();
                
                //$this->mailError();
            }
            else
            {
                $this->log();
                $this->mailError();
            }
            
            //throw new Exception('<b>MySQL error:</b>' . $this->error . ', <br /><b>query> <br/>' . $this->queryStr);
            return;
        }
    
    }

    private function __multiQueriesConstruct ($queries, $transaction = FALSE)
    {
        
        if ($transaction)
        {
            $this->startTransaction();
            try
            {
                foreach ($queries as $query)
                {
                    $this->__singleQueryConstruct($query);
                }
                $this->commit();
                $this->endTransaction();
            }
            catch (Exception $e)
            {
                $this->rollback();
                $this->endTransaction();
                throw new Exception($e->getMessage());
            }
        }
        else
        {
            foreach ($queries as $query)
            {
                $this->__singleQueryConstruct($query);
            }
        }
    }

    private function execute ()
    {
        $this->logQuery();
        
        switch ($this->queryType)
        {
            case 'SELECT':
                $result = $this->executeSelect($this->queryStr);
                break;
            case 'UPDATE':
                $result = $this->executeUpdate($this->queryStr);
                break;
            case 'INSERT':
                $result = $this->executeInsert($this->queryStr);
                break;
            case 'DELETE':
                $result = $this->executeDelete($this->queryStr);
                break;
            case 'REPLACE':
                $result = $this->executeReplace($this->queryStr);
                break;
            default:
                $result = mysql_query($this->queryStr, $this->connectionLink);
                break;
        }
        
        $this->errno = mysql_errno($this->connectionLink);
        $this->error = mysql_error($this->connectionLink);
        $this->result = $result;
    }

    public function executeQuery ($queryStr)
    {
        $this->__singleQueryConstruct($queryStr);
    
    }

    public function startTransaction ()
    {
        new Query('SET AUTOCOMMIT=0');
        new Query('START TRANSACTION');
    }

    public function endTransaction ()
    {
        new Query('SET AUTOCOMMIT=1');
    }

    public function commit ()
    {
        new Query("COMMIT");
        new Query('SET AUTOCOMMIT=1');
    }

    public function rollback ()
    {
        new Query("ROLLBACK");
        new Query('SET AUTOCOMMIT=1');
    }

    /**
     *
     * @param <boolean> $flag
     */
    public function setAutocommit ($flag)
    {
        if ($flag)
        {
            new Query('SET AUTOCOMMIT=1');
        }
        else
        {
            new Query('SET AUTOCOMMIT=0');
        }
    }

    /**
     *
     *
     * @return <resource>
     */
    private function executeSelect ($queryStr)
    {
        
        if (strstr(strtoupper($queryStr), 'LIMIT'))
        {
            $result = mysql_query('SELECT SQL_CALC_FOUND_ROWS ' . substr(ltrim($queryStr), 6), $this->connectionLink);
        
        }
        else
        {
            
            $result = mysql_query($queryStr, $this->connectionLink);
        }
        
        $this->rowsNum = mysql_num_rows($result);
        return $result;
    }

    private function caculateFoundRows ()
    {
        $query = new Query('Select FOUND_ROWS() as total');
        $this->rowsFound = $query->fetchField('total');
    }

    public function getRowsFound ()
    {
        return $this->rowsFound;
    }

    /**
     *
     * @return <resource>
     */
    private function executeUpdate ($queryStr)
    {
        $result = mysql_query($queryStr, $this->connectionLink);
        if ($result)
        {
            $this->affectedRows = mysql_affected_rows($this->connectionLink);
        }
        return $result;
    }

    /**
     *
     * @return <resource>
     */
    private function executeInsert ($queryStr)
    {
        
        $result = mysql_query($queryStr, $this->connectionLink);
        if ($result)
        {
            $this->lastInsertID = mysql_insert_id($this->connectionLink);
        }
        return $result;
    }

    /**
     *
     * @return <resource>
     */
    private function executeDelete ($queryStr)
    {
        $result = mysql_query($queryStr, $this->connectionLink);
        if ($result)
        {
            $this->affectedRows = mysql_affected_rows($this->connectionLink);
        }
        return $result;
    }

    private function executeReplace ($queryStr)
    {
        
        $result = mysql_query($queryStr, $this->connectionLink);
        if ($result)
        {
            $this->affectedRows = mysql_affected_rows($this->connectionLink); //if affected rows is tow then it is replaced otherwise,1, it is new insert
            $this->lastInsertID = mysql_insert_id($this->connectionLink);
        }
        return $result;
    }

    /*
 * @return array
 */
    public function fetchRow ($type = MYSQL_ASSOC)
    {
        
        switch ($type)
        {
            case MYSQL_BOTH: //3
                $row = mysql_fetch_array($this->result, MYSQL_BOTH);
                break;
            case MYSQL_ASSOC: //1
                $row = mysql_fetch_array($this->result, MYSQL_ASSOC);
                break;
            case MYSQL_NUM: //2
            default:
                $row = mysql_fetch_row($this->result);
        }
        
        if ($this->stripSlashes)
        {
            // strip all slashes out of row...
            foreach ($row as $key => $value)
            {
                $row[$key] = stripslashes($value);
            }
        }
        
        return $row;
    
    }

    public function fetchRows ($type = MYSQL_ASSOC)
    {
        $rows = array();
        while ($record = mysql_fetch_array($this->result, $type))
        {
            $rows[] = $record;
        }
        return $rows;
    }

    /*
 * @return array
 */
    public function fetchRowsByID ()
    {
        $rows = array();
        while ($record = mysql_fetch_array($this->result, $type))
        {
            $rows[$record['ID']] = $record;
        }
        return $rows;
    }

    /*
     * @retun mixed
     */
    public function fetchField ($fieldName)
    {
        $row = $this->fetchRow();
        return $row[$fieldName];
    }

    private function normalizeValue ($value)
    {

    }

    private function stringQuote ($queryStr)
    {
        
        if (is_numeric($queryStr))
        {
            $queryStr = $queryStr / 1;
            return $queryStr;
        }
        $queryStr = trim($queryStr . ' ');
        if (get_magic_quotes_gpc())
        {
            $queryStr = stripslashes($queryStr);
        }
        $queryStr = mysql_real_escape_string($queryStr);
        return $queryStr;
    }

    /**
     * To get the type of the query passed.
     *
     * @param string
     * @return string
     */
    private function queryType ($queryStr)
    {
        
        if (strtoupper(substr(trim($queryStr), 0, 6)) == 'SELECT')
        {
            $this->queryType = 'SELECT';
        }
        elseif (strtoupper(substr(trim($queryStr), 0, 6)) == 'UPDATE')
        {
            $this->queryType = 'UPDATE';
        }
        elseif (strtoupper(substr(trim($queryStr), 0, 6)) == 'INSERT')
        {
            $this->queryType = 'INSERT';
        }
        elseif (strtoupper(substr(trim($queryStr), 0, 6)) == 'DELETE')
        {
            $this->queryType = 'DELETE';
        }
        elseif (strtoupper(substr(trim($queryStr), 0, 7)) == 'REPLACE')
        {
            $this->queryType = 'REPLACE';
        }
        
        return $this->queryType;
    }

    /**
     * If the query from DELETE or UPDATE type we get the number affected rows.
     *
     * @return integer number of affected rows
     */
    public function getAffectedRows ()
    {
        return $this->affectedRows;
    }

    public function getResult ()
    {
        return $this->result;
    }

    public function reset ()
    {
        
        $classAttributes = get_class_vars(__CLASS__);
        
        foreach ($classAttributes as $attribute => $defaultValue)
        {
            if ($attribute == 'dbc' || $attribute == 'connectionLink')
            {
                continue;
            }
            $this->$attribute = $defaultValue;
        }
    }

    /**
     * To get number of rows fetched by SELECT.
     * @return <int>
     */
    public function getNumRows ()
    {
        return mysql_num_rows($this->result);
    }

    public function count ()
    {
        return mysql_num_rows($this->result);
    }

    /**
     * This function to get the las inster ID done throught the same connection.
     * @return <int>
     */
    public function getLastInsertID ()
    {
        if ($this->queryType == 'INSERT')
        {
            return $this->lastInsertID;
        }
        else
        {
            
            return FALSE;
        }
    
    }

    public function __destruct ()
    {
        $this->free();
    

     //global $db;
    //$db->destroyDbc();
    }

    public function getErrorNo ()
    {
        return $this->errno;
    }

    public function getError ()
    {
        //        $trace = debug_backtrace();
        //
        //        $caller = array_shift($trace);
        //
        //        echo 'called by ' . $caller['function'] . ' function';
        //        if (isset($caller['class']))
        //            echo ' in ' . $caller['class'];
        return '<br>MySQL error:' . $this->error . ', <br>query=' . $this->queryStr;
    }

    public function getQuery ()
    {
        return $this->queryStr;
    }

    public function printQuery ()
    {
        echo '<pre>';
        print_r($this->queryStr);
        echo '</pre>';
    }

    private function free ()
    {
        
        if (is_resource($this->result))
        {
            mysql_free_result($this->result);
        }
    
    }

    //	private function reset()
    //	{
    //	    foreach($this as $attribute => &$value)
    //	    {
    //            $value = NULL;
    //	    }
    //	}
    


    /**
     * This function to resept the pointer to the first record in result resource.
     */
    public function resetPointer ()
    {
        mysql_data_seek($this->result, 0);
    }

    function dumpResult ($rowsCount = 50)
    {
        
        // Useful during development for debugging  purposes.  Simple dumps a
        // query to the screen in a table.
        


        if (! $this->result)
        {
            return 'result empty';
        }
        elseif ($this->queryType != 'SELECT')
        {
            return 'No output for such query';
        }
        
        echo "<table cellpadding=\"3\" cellspacing=\"1\" border=\"0\" width=\"100%\"  style=\"border: 1px solid blue; font-family: sans-serif;\">\n";
        
        $i = 0;
        $header = '';
        while ($row = $this->fetchRow())
        {
            if ($i == 0)
            {
                $header .= "<tr><td colspan=\"" . (sizeof($row) + 1) . "\"><span style=\"font-face: monospace; font-size: 9pt;\">$sql</span></td></tr>\n";
                $header .= "<tr>\n";
                $header .= "<td bgcolor=\"#E6E5FF\"><span style=\"font-face: sans-serif; font-size: 9pt; font-weight: bold;\">###</span></td>\n";
                foreach ($row as $col => $value)
                {
                    $header .= "<td bgcolor=\"#E6E5FF\"><span style=\"font-face: sans-serif; font-size: 9pt; font-weight: bold;\">$col</span></td>\n";
                }
                $header .= "</tr>\n";
            }
            if ($i % $rowsCount == 0)
            {
                echo $header;
            }
            $i ++;
            if ($i % 2 == 0)
                $bg = '#E3E3E3';
            else
                $bg = '#F3F3F3';
            echo "<tr>\n";
            echo "<td bgcolor=\"$bg\"><span style=\"font-face: sans-serif; font-size: 9pt;\">$i</span></td>\n";
            foreach ($row as $value)
            {
                echo "<td bgcolor=\"$bg\"><span style=\"font-face: sans-serif; font-size: 9pt;\">$value</span></td>\n";
            }
            echo "</tr>\n";
        }
        echo "</table>";
    }

    private function logQuery ()
    {
        //DatabaseConnection::getInstance()->logQuery($this->queryStr);
    }

    //Query Builder Functions
    public function select ($fld)
    {
        $this->fields[] = $fld;
    }

    public function val ($key, $val)
    {
        
        if (is_null($val))
        {
            $this->raw($key, 'NULL');
        }
        elseif (is_string($val))
        {
            
            if ($val == '0')
            {
                $this->raw($key, '0');
            }
            elseif (trim($val) == '')
            {
                $this->raw($key, 'NULL');
            }
            else
            {
                $val = trim($val);
                $val = $this->stringQuote($val);
                $this->values[] = "$key = '$val'";
            }
        }
        elseif (is_numeric($val))
        {
            $this->raw($key, $val);
        }
    }

    public function raw ($key, $val)
    {
        $this->values[] = "$key = $val";
    }

    public function arr ($arr)
    {
        foreach ($arr as $key => $val)
            $this->val($key, $val);
    }

    public function table ($tbl)
    {
        $this->from($tbl);
    }

    public function from ($tbl)
    {
        $this->table[] = $tbl;
    }

    public function join ($type, $tbl, $on)
    {
        $this->joins[] = strtoupper($type) . " JOIN $tbl ON $on";
    }

    public function where ($cnd, $val = "", $op = "=")
    {
        
        $val = trim($val);
        
        $cnd = trim($cnd);
        
        $val = $this->stringQuote($val);
        
        if ($val == '')
        {
            
            if (preg_match("/^[a-zA-Z]+[a-zA-Z_]*[a-zA-Z]+$/", $cnd))
            {
                throw new Exception('Value of this field ' . $cnd . ' is missing! please check your query');
                $this->where[] = "$cnd = NO_VALUE";
            }
            else
            {
                $this->where[] = $cnd;
            }
        }
        else
        {
            $this->where[] = $cnd . ($val !== '' ? " $op '$val'" : "");
        }
    
    }

    public function having ($cnd, $val = "", $op = "=")
    {
        
        $val = trim($val);
        
        $cnd = trim($cnd);
        
        $val = $this->stringQuote($val);
        
        if ($val == '')
        {
            
            if (preg_match("/^[a-zA-Z]+[a-zA-Z_]*[a-zA-Z]+$/", $cnd))
            {
                throw new Exception('Value of this field ' . $cnd . ' is missing! please check your query');
                $this->having[] = "$cnd = NO_VALUE";
            }
            else
            {
                $this->having[] = $cnd;
            }
        }
        else
        {
            $this->having[] = $cnd . ($val !== '' ? " $op '$val'" : "");
        }
    
    }

    public function group ($grp)
    {
        $this->group = $grp;
    }

    public function order ($ord, $asc = "ASC")
    {
        $this->order[] = "$ord $asc";
    }

    public function limit ($row, $max = 0)
    {
        $this->limit = $row . ($max ? ", " . $max : "");
    }

    public function setQuery ($str)
    {
        $this->queries[] = $str;
    }

    public function setDistinct ($distinct = true)
    {
        $this->distinct = $distinct;
    }

    public function setIgnore ($ignore = true)
    {
        $this->ignore = $ignore;
    }

    public function build ($method)
    {
        switch (strtoupper($method))
        {
            case "SELECT":
                $this->queryStr = "SELECT " . ($this->distinct ? "DISTINCT " : "") . "\n" . ($this->fields ? implode(", ", $this->fields) : "*") . " \n" . "FROM " . implode(", ", $this->table) . " \n" .
                 ($this->joins ? implode("\n", $this->joins) . " \n" : "") . ($this->where ? "WHERE " . implode(" AND ", $this->where) . " \n" : "") .
                 ($this->group ? "GROUP BY " . $this->group . " \n" : "") . ($this->having ? "HAVING " . implode(" AND ", $this->having) . " \n" : "") .
                 ($this->order ? "ORDER BY " . implode(", ", $this->order) . " \n" : "") . ($this->limit ? "LIMIT " . $this->limit . " \n" : "");
                break;
            case "INSERT":
                $this->queryStr = "INSERT  " . ($this->ignore ? "IGNORE " : "") . "INTO " . implode(", ", $this->table) . " SET \n" . implode(",\n", $this->values) . " \n";
                break;
            case "UPDATE":
                $this->queryStr = "UPDATE " . implode(", ", $this->table) . " SET \n" . implode(",\n", $this->values) . " \n" . ($this->where ? "WHERE " . implode(" AND ", $this->where) . " \n" : "") .
                 ($this->limit ? "LIMIT " . $this->limit . " \n" : "");
                break;
            case "DELETE":
                $this->queryStr = "DELETE FROM " . implode(", ", $this->table) . " \n" . ($this->where ? "WHERE " . implode(" AND ", $this->where) . " \n" : "") .
                 ($this->order ? "ORDER BY " . implode(", ", $this->order) . " \n" : "") . ($this->limit ? "LIMIT " . $this->limit . " \n" : "");
                break;
        }
    }

    public function exec ($method = "", $transaction = FALSE, $silent = FALSE)
    {
        if ($method)
        {
            $this->build($method);
        }
        /*else
        {
            $this->build($this->queryType($this->queryStr));
        }*/
        
        if ($transaction)
        {
            $this->__singleQueryConstruct($this->queryStr, TRUE);
        }
        else
        {
            $this->__singleQueryConstruct($this->queryStr, FALSE);
        }
    }

    public function error ()
    {
        if ($this->errno)
        {
            return true;
        }
        return false;
    }

    private function mailError ()
    {
        $to = "muhammad.naneh@ssense.com";
        $subject = "Error";
        

        $debugBackTrace = debug_backtrace();
        foreach ($debugBackTrace as $key => $value)
        {
            if ($value['class'] == 'Query')
            {
                unset($debugBackTrace[$key]);
            }
        }
        $debugBackTrace = print_r($debugBackTrace, true);
        $page = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
        $message = "\r\n\r\n" . '<pre>' . $debugBackTrace . '<br>URL: ' . $page . '<br>' . $this->getError();
        
        $email = new mail_sender();
        $email->set_ms_to($to);
        //$email->set_ms_bcc("receipts@ssense.com");
        $email->set_ms_from("ssense.com - < error.query@ssense.com >");
        
        $email->set_ms_subject('ssense.com - ' . $subject);
        //$email->set_ms_body_text($message);
        $email->set_ms_body_html($message);
        $email->set_ms_mode('');
        $email->set_ms_charset('utf-8');
        $email->set_ms_body();
        $email->ms_send();
    

    }

    private function log ()
    {
        global $err;
        $err->error($this->getError());
    }


}
?>